﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using VswService_V7.Utility;

namespace VswService_V7.DataBase.Tables
{
    public class TEAM_TABLE
    {
		#region  field
		public static readonly string tableName = "TEAM_TABLE";
		public static readonly string fd_shiftDate = "SHIFT_DATE";
		public static readonly string fd_dayShift = "DAY_SHIFT";
		public static readonly string fd_nightShift = "NIGHT_SHIFT";
		#endregion
		/// <summary>
		/// replace all teams
		/// </summary>
		/// <param name="dataTable"></param>
		/// <returns></returns>
		public static DataTable ResetTeamTable(DataTable dataTable)
		{
			try
			{
				using (OracleConnection conn = new OracleConnection(OracleHelper.oracleConnectionStringBuilder.ConnectionString))
				{
					conn.Open();
					using (OracleTransaction trans = conn.BeginTransaction())
					{
						try
						{
							string deleteSQL = "delete from " + tableName;
							using (OracleCommand cmd = new OracleCommand(deleteSQL, conn))
							{
								cmd.CommandType = CommandType.Text;
								int result = cmd.ExecuteNonQuery();
								if (result < 0)
								{
									trans.Rollback();
									trans.Commit();
									return null;
								}
							}

							StringBuilder insertsql = new StringBuilder();
							insertsql.Append("insert all");
							foreach (DataRow item in dataTable.Rows)
							{
								string s1 = string.Format(" into {0}({1},{2},{3})", tableName, fd_shiftDate, fd_dayShift, fd_nightShift);
								insertsql.Append(s1);
								string s2 = string.Format("('{0}','{1}','{2}')",
									item[fd_shiftDate].ToString(), item[fd_dayShift].ToString(), item[fd_nightShift]);
								insertsql.Append(" values" + s2);
							}
							insertsql.Append(" select* from dual");
							using (OracleCommand cmd = new OracleCommand(insertsql.ToString(), conn))
							{
								cmd.CommandType = CommandType.Text;
								int result = cmd.ExecuteNonQuery();
								if (result > 0)
								{
									trans.Commit();
								}
								if (result <= 0)
								{
									trans.Rollback();
									trans.Commit();
									return null;
								}
							}
						}
						catch
						{
							trans.Rollback();
							trans.Commit();
							return null;
						}
					}
					string selectSQL = "select * from " + tableName;
					return OracleHelper.Exct_QueryTable(selectSQL);
				}
			}
			catch
			{
				return null;
			}
		}
		/// <summary>
		/// Get the current time group
		/// </summary>
		/// <param name="shiftTable"></param>
		/// <param name="teamTable"></param>
		/// <param name="dateTime"></param>
		/// <returns></returns>
		public static string GetCurrentShift(DateTime dateTime)
		{
			try
			{
				DataTable shiftTable = OracleHelper.Exct_QueryTable(string.Format("select * from {0}", SHIFT_TABLE.tableName));
				DataTable teamTable = OracleHelper.Exct_QueryTable(string.Format("select * from {0}", tableName));
				if (shiftTable != null && teamTable != null && shiftTable.Rows.Count > 0 && teamTable.Rows.Count > 0)
				{
					string shiftName = "";
					//第一个班组的时间
					DateTime firstShiftdateTime = Convert.ToDateTime(teamTable.Rows[0][TEAM_TABLE.fd_shiftDate].ToString());
					//当前时间
					DateTime currentDate = Convert.ToDateTime(dateTime.ToString(Formats.GetShortFormat()));
					//当前时间与第一个班组的时间天数差
					int days = currentDate.Subtract(firstShiftdateTime).Days;
					if (days > 0)
					{
						//天数差跟TEAM_TABLE数据行数取余
						int remainder = days % teamTable.Rows.Count;

						//判断当前时间属于白班还是夜班
						string dayOrNightShift = "";
						for (int i = 0; i < shiftTable.Rows.Count; i++)
						{
							DateTime startTime = Convert.ToDateTime(shiftTable.Rows[i][SHIFT_TABLE.fd_startTime].ToString());
							DateTime endTime = Convert.ToDateTime(shiftTable.Rows[i][SHIFT_TABLE.fd_endTime].ToString());
							DateTime currentTime = Convert.ToDateTime(dateTime.ToString(Formats.GetTimeFormat()));
							if (startTime < endTime)
							{
								if (currentTime > startTime && currentTime < endTime)
								{
									dayOrNightShift = shiftTable.Rows[i][SHIFT_TABLE.fd_shiftName].ToString();
								}
							}
							else
							{
								if (currentTime > startTime || currentTime < endTime)
								{
									dayOrNightShift = shiftTable.Rows[i][SHIFT_TABLE.fd_shiftName].ToString();
								}
							}
						}
						if (dayOrNightShift == "DAY")
						{
							shiftName = teamTable.Rows[remainder][TEAM_TABLE.fd_dayShift].ToString();
						}
						else if (dayOrNightShift == "NIGHT")
						{
							shiftName = teamTable.Rows[remainder][TEAM_TABLE.fd_nightShift].ToString();
						}
					}
					
					return shiftName;
				}
				else
				{
					return "";
				}
				
			}
			catch (Exception ex)
			{
				LogHelper.WriteLog("GetCurrentShift", ex);
				throw ex;
			}			
		}

		
	}
}
